package com.ljj;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;

public class ExcelReader {
    public static void main(String[] args) throws IOException {
        String filePath = "path/to/your/excel/file.xlsx";
        filePath = "F:\\readExcel\\带有合并数据的.xlsx";

        try (FileInputStream fis = new FileInputStream(new File(filePath));
             Workbook workbook = new XSSFWorkbook(fis)) {

            Sheet sheet = workbook.getSheetAt(0); // Assuming you want to read the first sheet
            // Iterate over each row
            for (Row row : sheet) {
                // Iterate over each cell
                for (Cell cell : row) {
                    // Handle merged cells
                    if ((cell.getCellStyle().getAlignmentEnum() == HorizontalAlignment.CENTER
                            ||cell.getCellStyle().getAlignmentEnum() == HorizontalAlignment.LEFT
                            || cell.getCellStyle().getAlignmentEnum()== HorizontalAlignment.RIGHT
                            || cell.getCellStyle().getAlignment()==HorizontalAlignment.DISTRIBUTED) &&
                            cell.getStringCellValue().isEmpty()) {
                        // This could indicate a merged cell, find the actual value
                        String realValue = getMergedCellValue(sheet, row.getRowNum(), cell.getColumnIndex());
                        System.out.println("Real Value at (" + row.getRowNum() + "," + cell.getColumnIndex() + "): " + realValue);
                    } else {
                        // Regular cell processing
                        System.out.println("Value at (" + row.getRowNum() + "," + cell.getColumnIndex() + "): " +
                                getCellValueAsString(cell));
                    }
                }
            }
        }
    }

    private static String getMergedCellValue(Sheet sheet, int rowNum, int colNum) {
        for (CellRangeAddress mergedRegion : sheet.getMergedRegions()) {
            if (mergedRegion.isInRange(rowNum, colNum)) {
                Row mergeFirstRow = sheet.getRow(mergedRegion.getFirstRow());
                Cell mergedCell = mergeFirstRow.getCell(mergedRegion.getFirstColumn());
                return getCellValueAsString(mergedCell);
            }
        }
        return "";
    }

    private static String getCellValueAsString(Cell cell) {
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                return String.valueOf(cell.getNumericCellValue());
            // Handle other cell types as needed
            default:
                return "";
        }
    }
}
